package com.yang.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.yang.entity.Student;
import com.yang.util.ConnectionFactory;
/**
 * 处理学生类的业务类
 * @author 杨吉明
 *
 */
public class DealStudent {
	
	/**
	 * 插入数据
	 * @param conn
	 * @param stu
	 * @throws SQLException
	 */
	public void insert(Connection conn,Student stu) throws SQLException{
		String sql = "INSERT INTO t_students (stu_id, stu_name, stu_age, stu_gender, stu_adress) VALUES(?,?,?,?,?)";
		PreparedStatement prep = conn.prepareStatement(sql);
		prep.setLong(1, stu.getId());
		prep.setString(2, stu.getName());
		prep.setLong(3, stu.getAge());
		prep.setLong(4, stu.getGender());
		prep.setString(5, stu.getAddress());
		prep.execute();
		ConnectionFactory.getInstance().close(null, prep, null);
	}
	
	/**
	 * 通过年龄查询
	 * @param conn
	 * @param stu
	 * @throws SQLException
	 */
	public void queryByAge(Connection conn,Student stu) throws SQLException{
		String sql = " SELECT * FROM t_students WHERE stu_age>? and stu_gender=1";
		PreparedStatement prep = conn.prepareStatement(sql);
		prep.setLong(1, stu.getAge());
		ResultSet result = prep.executeQuery();
		while(result.next()){
			System.out.print(result.getString("stu_id")+"\t");
			System.out.print(result.getString("stu_name")+"\t");
			System.out.print(result.getString("stu_age")+"\t");
			System.out.print(result.getString("stu_gender")+"\t");
			System.out.println(result.getString("stu_adress")+"\t");
		}
		ConnectionFactory.getInstance().close(result, prep, null);
	}
	
	/**
	 * 查询所有数据
	 * @param conn
	 * @param stu
	 * @throws SQLException
	 */
	public void queryAll(Connection conn) throws SQLException{
		String sql = " SELECT * FROM t_students";
		PreparedStatement prep = conn.prepareStatement(sql);
		ResultSet result = prep.executeQuery(sql);
		while(result.next()){
			System.out.print(result.getString("stu_id")+"\t");
			System.out.print(result.getString("stu_name")+"\t");
			System.out.print(result.getString("stu_age")+"\t");
			System.out.print(result.getString("stu_gender")+"\t");
			System.out.println(result.getString("stu_adress")+"\t");
		}
		ConnectionFactory.getInstance().close(result, prep, null);
	}
	
	/**
	 * 删除数据
	 * @param conn
	 * @param stu
	 * @throws SQLException
	 */
	public void delete(Connection conn,Student stu) throws SQLException{
		String sql = " DELETE FROM t_students WHERE stu_gender=?";
		PreparedStatement prep = conn.prepareStatement(sql);
		prep.setLong(1, stu.getGender());
		prep.execute();
		ConnectionFactory.getInstance().close(null, prep, null);
	}
	
	/**
	 * 删除所有数据
	 * @param conn
	 * @param stu
	 * @throws SQLException
	 */
	public void deleteAll(Connection conn) throws SQLException{
		String sql = " DELETE FROM t_students";
		PreparedStatement prep = conn.prepareStatement(sql);
		prep.execute();
		ConnectionFactory.getInstance().close(null, prep, null);
	}
}
